
DECLARE @L0 uniqueidentifier,@L2 uniqueidentifier,@DN nvarchar(260),@L3 uniqueidentifier;

SET @L0='00000000-0000-0000-0000-000000000000';
SET @L2='91ED7715-7EE3-4304-A825-89F9850B464A'
SET @DN=N'rollup/test1/Lists/TaskList';
SET @L3='0CBBE27C-E6AD-43BE-8CA9-5DA426CDAED7';

WITH InternalTable AS 
(
SELECT   TOP 101 
                           UserData.[tp_ID],
                           --UserData.[tp_IsCurrentVersion],
                           --UserData.[tp_Level],
                           UserData.[tp_SiteId],
                           UserData.[tp_LeafName],
                           --UserData.[tp_CalculatedVersion],
                           UserData.[nvarchar1]--,
                           --UserData.[tp_HasCopyDestinations],
                           --UserData.[int4],
                           --UserData.[tp_DirName],
                           --UserData.[nvarchar2],
                           --UserData.[tp_DeleteTransactionId]
						   --UserData.[nvarchar6]  AS c6c7                           
          FROM     UserData                   
          WHERE    UserData.tp_ListID = @L3
                   AND ((UserData.tp_IsCurrent = 1))
                   AND UserData.tp_SiteId = @L2
                   AND (UserData.tp_DirName = @DN)
                   AND UserData.tp_RowOrdinal = 0                   
          ORDER BY UserData.[tp_ID] Asc
)


SELECT   DISTINCT InternalTable.*
				  --,t3.tp_Id	,
                  --t2.[tp_Ordinal],
                  --,t2.[tp_ID]      AS c6
				  ,t5.[nvarchar6]  AS c6c7
FROM   InternalTable
         LEFT OUTER JOIN UserDataJunctions AS t2
           ON InternalTable.[tp_SiteId] = t2.[tp_SiteId]
              --AND t3.[tp_DeleteTransactionId] = t2.[tp_DeleteTransactionId]
              --AND t3.[tp_IsCurrentVersion] = t2.[tp_IsCurrentVersion]
              --AND t3.[tp_DirName] = t2.[tp_DirName]
              AND InternalTable.[tp_LeafName] = t2.[tp_LeafName]
              --AND t3.[tp_CalculatedVersion] = t2.[tp_CalculatedVersion]
              --AND t3.[tp_Level] = t2.[tp_Level]              
			  AND t2.[tp_FieldId] = '{fdda84e2-205c-42dc-9f8a-16264110a70e}'
         LEFT OUTER JOIN AllUserData AS t5 WITH (NOLOCK ,INDEX = AllUserData_PK)
           ON (t5.[tp_ListId] = @L3
               AND t5.[tp_Id] = t2.[tp_ID])
ORDER BY InternalTable.tp_ID Asc--, t2.[tp_Ordinal]
--OPTION (FORCE ORDER)
--GROUP BY t3.tp_ID